

using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using CoreCms.Net.Configuration;
using CoreCms.Net.IRepository;
using CoreCms.Net.IRepository.UnitOfWork;
using CoreCms.Net.IServices;
using CoreCms.Net.Model.Entities;
using CoreCms.Net.Model.ViewModels.Basics;
using CoreCms.Net.Model.ViewModels.Echarts;
using CoreCms.Net.Model.ViewModels.UI;
using CoreCms.Net.Utility.Helper;
using SqlSugar;


namespace CoreCms.Net.Services
{
    /// <summary>
    /// 报表 接口实现
    /// </summary>
    public class CoreCmsReportsServices : BaseServices<GetOrdersReportsDbSelectOut>, ICoreCmsReportsServices
    {
        private readonly ICoreCmsReportsRepository _dal;
        private readonly IUnitOfWork _unitOfWork;
        public CoreCmsReportsServices(IUnitOfWork unitOfWork, ICoreCmsReportsRepository dal)
        {
            this._dal = dal;
            base.BaseDal = dal;
            _unitOfWork = unitOfWork;
        }

        /// <summary>
        /// 订单报表
        /// </summary>
        /// <param name="num">数量</param>
        /// <param name="where">查询条件</param>
        /// <param name="section">查询值</param>
        /// <param name="sTime">开始时间</param>
        /// <param name="joinVal">筛选字段createTime/paymentTime </param>
        /// <returns></returns>
        public List<GetOrdersReportsDbSelectOut> GetOrderMark(int num, string where, int section, DateTime sTime, string joinVal)
        {

            var sqlStr = string.Empty;
            string dbTypeString = AppSettingsConstVars.DbDbType;
            if (dbTypeString == DbType.SqlServer.ToString())
            {
                sqlStr = @"SELECT  tmp_x.number ,
                                ISNULL(SUM(o.orderAmount), 0) AS val ,
                                COUNT(o.orderId) AS num
                        FROM    ( ( SELECT  number
                                    FROM    MASTER..spt_values
                                    WHERE   TYPE = 'P'
                                            AND number >= 0
                                            AND number <= " + num + @"
                                  ) tmp_x
                                  LEFT OUTER JOIN ( SELECT  *
                                                    FROM    CoreCmsOrder
                                                    WHERE isdel=0  " + where + @"
                                                  ) o ON tmp_x.number = DATEDIFF(" + (section == 3600 ? "HOUR" : "DAY") + ", '" + sTime.ToString("yyyy-MM-dd HH:mm:ss") + @"', o." + joinVal + @")
                                )
                        GROUP BY tmp_x.number";
            }
            else if (dbTypeString == DbType.MySql.ToString())
            {
                sqlStr = @"select tmp_x.x,ifnull(sum(o.orderAmount),0) as val,count(o.orderId) as num
                from
                  (SELECT @xi:=@xi+1 as x from
                    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
                    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
                    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
                    (SELECT @xi:=-1) x0 limit 0," + num + @") tmp_x
                    left join (
                        select * from CoreCmsOrder
                    where 1 = 1
                    " + where + @"
                        ) o on tmp_x.x = ((unix_timestamp(o." + joinVal + @") - " + CommonHelper.ConvertDateTimeToInt(sTime) + @") div(" + section + @"))
                    group by tmp_x.x
                    ";
            }
            Console.WriteLine(sqlStr);
            var sp = new List<SugarParameter>();

            var list = _dal.SqlQuery(sqlStr, sp);

            return list;
        }


        /// <summary>
        /// 支付单报表
        /// </summary>
        /// <param name="num">数量</param>
        /// <param name="where">查询条件</param>
        /// <param name="section">查询值</param>
        /// <param name="sTime">开始时间</param>
        /// <param name="joinVal">筛选字段createTime/paymentTime </param>
        /// <returns></returns>
        public List<GetOrdersReportsDbSelectOut> GetPaymentsMark(int num, string where, int section, DateTime sTime, string joinVal)
        {
            var sqlStr = string.Empty;
            string dbTypeString = AppSettingsConstVars.DbDbType;
            if (dbTypeString == DbType.SqlServer.ToString())
            {
                sqlStr = @"SELECT  tmp_x.number ,
                                ISNULL(SUM(o.money), 0) AS val ,
                                COUNT(o.paymentId) AS num
                        FROM    ( ( SELECT  number
                                    FROM    MASTER..spt_values
                                    WHERE   TYPE = 'P'
                                            AND number >= 0
                                            AND number <= " + num + @"
                                  ) tmp_x
                                  LEFT OUTER JOIN ( SELECT  *
                                                    FROM    CoreCmsBillPayments
                                                    WHERE 1=1  " + where + @"
                                                  ) o ON tmp_x.number = DATEDIFF(" + (section == 3600 ? "HOUR" : "DAY") + ", '" + sTime.ToString("yyyy-MM-dd HH:mm:ss") + @"', o." + joinVal + @")
                                )
                        GROUP BY tmp_x.number";
            }
            else if (dbTypeString == DbType.MySql.ToString())
            {
                sqlStr = @"select tmp_x.x,ifnull(sum(o.money),0) as val,count(o.paymentId) as num
            from
              (SELECT @xi:=@xi+1 as x from
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
                (SELECT @xi:=-1) x0 limit 0," + num + @") tmp_x 
            left join (
                    select * from CoreCmsBillPayments 
            where 1 = 1
                " + where + @"
                        ) o on tmp_x.x = ((unix_timestamp(o." + joinVal + @") - " + CommonHelper.ConvertDateTimeToInt(sTime) + @") div(" + section + @"))
                group by tmp_x.x
                ";
            }

            var sp = new List<SugarParameter>();


            var list = _dal.SqlQuery(sqlStr, sp);

            return list;
        }



        /// <summary>
        /// 退款单报表
        /// </summary>
        /// <param name="num">数量</param>
        /// <param name="where">查询条件</param>
        /// <param name="section">查询值</param>
        /// <param name="sTime">开始时间</param>
        /// <param name="joinVal">筛选字段createTime/paymentTime </param>
        /// <returns></returns>
        public List<GetOrdersReportsDbSelectOut> GetRefundMark(int num, string where, int section, DateTime sTime, string joinVal)
        {

            var sqlStr = string.Empty;
            string dbTypeString = AppSettingsConstVars.DbDbType;
            if (dbTypeString == DbType.SqlServer.ToString())
            {
                sqlStr = @"SELECT  tmp_x.number ,
                                ISNULL(SUM(o.money), 0) AS val ,
                                COUNT(o.refundId) AS num
                        FROM    ( ( SELECT  number
                                    FROM    MASTER..spt_values
                                    WHERE   TYPE = 'P'
                                            AND number >= 0
                                            AND number <= " + num + @"
                                  ) tmp_x
                                  LEFT OUTER JOIN ( SELECT  *
                                                    FROM    CoreCmsBillRefund
                                                    WHERE 1=1  " + where + @"
                                                  ) o ON tmp_x.number = DATEDIFF(" + (section == 3600 ? "HOUR" : "DAY") + ", '" + sTime.ToString("yyyy-MM-dd HH:mm:ss") + @"', o." + joinVal + @")
                                )
                        GROUP BY tmp_x.number";
            }
            else if (dbTypeString == DbType.MySql.ToString())
            {
                sqlStr = @"select tmp_x.x,ifnull(sum(o.money),0) as val,count(o.refundId) as num
            from
              (SELECT @xi:=@xi+1 as x from
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
                (SELECT @xi:=-1) x0 limit 0," + num + @") tmp_x
                left join(
                    select* from CoreCmsBillRefund
                where 1 = 1
                " + where + @"
                        ) o on tmp_x.x = ((unix_timestamp(o." + joinVal + @") - " + CommonHelper.ConvertDateTimeToInt(sTime) + @") div(" + section + @"))
                group by tmp_x.x
                ";
            }

            var sp = new List<SugarParameter>();

            var list = _dal.SqlQuery(sqlStr, sp);

            return list;
        }

        /// <summary>
        /// 用户提现报表
        /// </summary>
        /// <param name="num">数量</param>
        /// <param name="where">查询条件</param>
        /// <param name="section">查询值</param>
        /// <param name="sTime">开始时间</param>
        /// <param name="joinVal">筛选字段createTime/paymentTime </param>
        /// <returns></returns>
        public List<GetOrdersReportsDbSelectOut> GetTocashMark(int num, string where, int section, DateTime sTime, string joinVal)
        {

            var sqlStr = string.Empty;
            string dbTypeString = AppSettingsConstVars.DbDbType;
            if (dbTypeString == DbType.SqlServer.ToString())
            {
                sqlStr = @"SELECT  tmp_x.number ,
                                ISNULL(SUM(o.money), 0) AS val ,
                                COUNT(o.id) AS num
                        FROM    ( ( SELECT  number
                                    FROM    MASTER..spt_values
                                    WHERE   TYPE = 'P'
                                            AND number >= 0
                                            AND number <= " + num + @"
                                  ) tmp_x
                                  LEFT OUTER JOIN ( SELECT  *
                                                    FROM    CoreCmsUserTocash
                                                    WHERE 1=1  " + where + @"
                                                  ) o ON tmp_x.number = DATEDIFF(" + (section == 3600 ? "HOUR" : "DAY") + ", '" + sTime.ToString("yyyy-MM-dd HH:mm:ss") + @"', o." + joinVal + @")
                                )
                        GROUP BY tmp_x.number";
            }
            else if (dbTypeString == DbType.MySql.ToString())
            {
                sqlStr = @"select tmp_x.x,ifnull(sum(o.money),0) as val,count(o.id) as num
            from
              (SELECT @xi:=@xi+1 as x from
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
                (SELECT @xi:=-1) x0 limit 0," + num + @") tmp_x 
            left join (
                    select * from CoreCmsUserTocash 
            where 1 = 1
                " + where + @"
                        ) o on tmp_x.x = ((unix_timestamp(o." + joinVal + @") - " + CommonHelper.ConvertDateTimeToInt(sTime) + @") div(" + section + @"))
                group by tmp_x.x
                ";
            }

            var sp = new List<SugarParameter>();

            var list = _dal.SqlQuery(sqlStr, sp);

            return list;
        }

        /// <summary> 
        /// 获取订单销量查询返回结果
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="filter"></param>
        /// <param name="filterSed"></param>
        /// <param name="thesort"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public async Task<IPageList<GoodsSalesVolume>> GetGoodsSalesVolumes(string start, string end, string filter, string filterSed, string thesort, int pageIndex = 1, int pageSize = 5000)
        {
            return await _dal.GetGoodsSalesVolumes(start, end, filter, filterSed, thesort, pageIndex, pageSize);
        }


        /// <summary>
        /// 获取商品收藏查询返回结果
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="thesort"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public async Task<IPageList<GoodsCollection>> GetGoodsCollections(string start, string end, string thesort, int pageIndex = 1, int pageSize = 5000)
        {
            return await _dal.GetGoodsCollections(start, end, thesort, pageIndex, pageSize);

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="where"></param>
        /// <returns></returns>

        public List<GetOrdersReportsDbSelectOut> GetReportOrder(string start, string end, string where, string orderTypeName)
        {
            
            var sqlStr = string.Empty;
            //string sqlQuery = @"WITH FilteredOrders AS (SELECT  orderId, sysid,  orderAmount,  status FROM CoreCmsOrder  WHERE  orderTypeName ='"+orderTypeName+"' AND createTime BETWEEN '" + start+"' AND '"+end+"' AND (signTime BETWEEN '"+start+"' AND '"+end+"' OR signTime IS NULL)  AND (sendTime BETWEEN '"+start+"' AND '"+end+"' OR sendTime IS NULL) AND (completeTime BETWEEN '"+start+"' AND '"+end+"' OR completeTime IS NULL) AND (refundTime BETWEEN '"+start+"' AND '"+end+ "' OR refundTime IS NULL)) SELECT c.companyName, d.depName,  g.groupName, u.nickName, SUM(o.orderAmount) AS val, COUNT(o.orderId) AS num,SUM(CASE WHEN o.status = 8 THEN o.orderAmount ELSE 0 END) AS signedAmount, SUM(CASE WHEN o.status = 8 THEN 1 ELSE 0 END) AS signedCount, SUM(CASE WHEN o.status = 6 THEN o.orderAmount ELSE 0 END) AS sendAmount, SUM(CASE WHEN o.status = 6 THEN 1 ELSE 0 END) AS sendCount, SUM(CASE WHEN o.status = 2 THEN o.orderAmount ELSE 0 END) AS completeAmount,  SUM(CASE WHEN o.status = 2 THEN 1 ELSE 0 END) AS completeCount, SUM(CASE WHEN o.status = 9 THEN o.orderAmount ELSE 0 END) AS refundAmount, SUM(CASE WHEN o.status = 9 THEN 1 ELSE 0 END) AS refundCount  " +
            //    "FROM FilteredOrders o INNER JOIN  SysUser u ON o.sysid = u.id INNER JOIN  " +
            //    "CoreCmsGroup g ON u.groupId = g.id INNER JOIN CoreCmsDepartment d ON g.dId = d.id INNER JOIN   " +
            //    "CoreCmsCompany c ON d.companyid = c.id where 1=1 "+where+" GROUP BY  c.companyName, d.depName,g.groupName, u.nickName ORDER BY c.companyName, d.depName, g.groupName,u.nickName";
            string sqlQuery = @"
            CREATE TABLE #TempFilteredOrders (
                orderId BIGINT,
                sysid BIGINT,
                orderAmount DECIMAL(10, 2),
                status INT
            );

            INSERT INTO #TempFilteredOrders
            SELECT orderId, sysid, orderAmount, status 
            FROM CoreCmsOrder 
            WHERE orderTypeName = '"+orderTypeName+"'  AND createTime BETWEEN '" + start + "' AND '" + end + "' AND (signTime BETWEEN '" + start + "' AND '" + end + "' OR signTime IS NULL)" +
              " AND (sendTime BETWEEN '" + start + "' AND '" + end + "' OR sendTime IS NULL)" +
              "AND (completeTime BETWEEN '"+start+"' AND '"+end+"' OR completeTime IS NULL)" +
              " AND (refundTime BETWEEN '"+start+"' AND '"+end+"' OR refundTime IS NULL);" +
              "SELECT c.companyName, d.depName, g.groupName, u.nickName, SUM(o.orderAmount) AS val, COUNT(o.orderId) AS num," +
              " SUM(CASE WHEN o.status = 8 THEN o.orderAmount ELSE 0 END) AS signedAmount," +
              " SUM(CASE WHEN o.status = 8 THEN 1 ELSE 0 END) AS signedCount," +
              " SUM(CASE WHEN o.status = 6 THEN o.orderAmount ELSE 0 END) AS sendAmount," +
              "SUM(CASE WHEN o.status = 6 THEN 1 ELSE 0 END) AS sendCount," +
              " SUM(CASE WHEN o.status = 2 THEN o.orderAmount ELSE 0 END) AS completeAmount," +
              "  SUM(CASE WHEN o.status = 2 THEN 1 ELSE 0 END) AS completeCount," +
              "  SUM(CASE WHEN o.status = 9 THEN o.orderAmount ELSE 0 END) AS refundAmount," +
              "  SUM(CASE WHEN o.status = 9 THEN 1 ELSE 0 END) AS refundCount" +
              "  FROM #TempFilteredOrders o INNER JOIN SysUser u ON o.sysid = u.id INNER JOIN CoreCmsGroup g ON u.groupId = g.id  " +
              "INNER JOIN CoreCmsDepartment d ON g.dId = d.id INNER JOIN CoreCmsCompany c ON d.companyid = c.id" +
              "  GROUP BY c.companyName, d.depName, g.groupName, u.nickName ORDER BY c.companyName, d.depName, g.groupName, u.nickName;  " +
              "DROP TABLE #TempFilteredOrders;";
            var sp = new List<SugarParameter>();

            var list = _dal.SqlQuery(sqlQuery, sp);
            return list;
             
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="where"></param>
        /// <returns></returns>

        public List<GetOrdersReportsDbSelectOut> GetReportOrderFG(string start, string end, string where, string orderTypeName)
        {

            var sqlStr = string.Empty;
            //string sqlQuery = @"CREATE TABLE #TempFilteredOrders (orderId BIGINT,sysid BIGINT, orderAmount DECIMAL(10, 2),status INT);INSERT INTO #TempFilteredOrders SELECT orderId, sysid, orderAmount, status FROM CoreCmsOrder where orderTypeName='"+orderTypeName+"' AND createTime BETWEEN '" + start + "' AND '" + end + "' AND (signTime BETWEEN '" + start + "' AND '" + end + "' OR signTime IS NULL)  AND (sendTime BETWEEN '" + start + "' AND '" + end + "' OR sendTime IS NULL) AND (completeTime BETWEEN '" + start + "' AND '" + end + "' OR completeTime IS NULL) AND (refundTime BETWEEN '" + start + "' AND '" + end + "' OR refundTime IS NULL)) SELECT c.companyName, d.depName,  g.groupName, u.nickName, SUM(o.orderAmount) AS val, COUNT(o.orderId) AS num,SUM(CASE WHEN o.status = 8 THEN o.orderAmount ELSE 0 END) AS signedAmount, SUM(CASE WHEN o.status = 8 THEN 1 ELSE 0 END) AS signedCount, SUM(CASE WHEN o.status = 6 THEN o.orderAmount ELSE 0 END) AS sendAmount, SUM(CASE WHEN o.status = 6 THEN 1 ELSE 0 END) AS sendCount, SUM(CASE WHEN o.status = 2 THEN o.orderAmount ELSE 0 END) AS completeAmount,  SUM(CASE WHEN o.status = 2 THEN 1 ELSE 0 END) AS completeCount, SUM(CASE WHEN o.status = 9 THEN o.orderAmount ELSE 0 END) AS refundAmount, SUM(CASE WHEN o.status = 9 THEN 1 ELSE 0 END) AS refundCount  " +
            //    "FROM FilteredOrders o INNER JOIN  SysUser u ON o.sysid = u.id INNER JOIN  " +
            //    "CoreCmsGroup g ON u.groupId = g.id INNER JOIN CoreCmsDepartment d ON g.dId = d.id INNER JOIN   " +
            //    "CoreCmsCompany c ON d.companyid = c.id where 1=1 " + where + " GROUP BY  c.companyName, d.depName,g.groupName, u.nickName ORDER BY c.companyName, d.depName, g.groupName,u.nickName DROP TABLE #TempFilteredOrders;";
            string sqlQuery = @"
            CREATE TABLE #TempFilteredOrders1 (
                orderId BIGINT,
                sysid BIGINT,
                orderAmount DECIMAL(10, 2),
                status INT
            );

            INSERT INTO #TempFilteredOrders1
            SELECT orderId, sysid, orderAmount, status 
            FROM CoreCmsOrder 
            WHERE orderTypeName = '" + orderTypeName + "'  AND createTime BETWEEN '" + start + "' AND '" + end + "' AND (signTime BETWEEN '" + start + "' AND '" + end + "' OR signTime IS NULL)" +
              " AND (sendTime BETWEEN '" + start + "' AND '" + end + "' OR sendTime IS NULL)" +
              "AND (completeTime BETWEEN '" + start + "' AND '" + end + "' OR completeTime IS NULL)" +
              " AND (refundTime BETWEEN '" + start + "' AND '" + end + "' OR refundTime IS NULL);" +
              "SELECT c.companyName, d.depName, g.groupName, u.nickName, SUM(o.orderAmount) AS val, COUNT(o.orderId) AS num," +
              " SUM(CASE WHEN o.status = 8 THEN o.orderAmount ELSE 0 END) AS signedAmount," +
              " SUM(CASE WHEN o.status = 8 THEN 1 ELSE 0 END) AS signedCount," +
              " SUM(CASE WHEN o.status = 6 THEN o.orderAmount ELSE 0 END) AS sendAmount," +
              "SUM(CASE WHEN o.status = 6 THEN 1 ELSE 0 END) AS sendCount," +
              " SUM(CASE WHEN o.status = 2 THEN o.orderAmount ELSE 0 END) AS completeAmount," +
              "  SUM(CASE WHEN o.status = 2 THEN 1 ELSE 0 END) AS completeCount," +
              "  SUM(CASE WHEN o.status = 9 THEN o.orderAmount ELSE 0 END) AS refundAmount," +
              "  SUM(CASE WHEN o.status = 9 THEN 1 ELSE 0 END) AS refundCount" +
              "  FROM #TempFilteredOrders1 o INNER JOIN SysUser u ON o.sysid = u.id INNER JOIN CoreCmsGroup g ON u.groupId = g.id  " +
              "INNER JOIN CoreCmsDepartment d ON g.dId = d.id INNER JOIN CoreCmsCompany c ON d.companyid = c.id" +
              "  GROUP BY c.companyName, d.depName, g.groupName, u.nickName ORDER BY c.companyName, d.depName, g.groupName, u.nickName;  " +
              "DROP TABLE #TempFilteredOrders1;";
            var sp = new List<SugarParameter>();

            var list = _dal.SqlQuery(sqlQuery, sp);
            return list;

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="where"></param>
        /// <returns></returns>

        public List<GetOrdersReportsDbSelectOut> GetReportOrderTD(string start, string end, string where, string orderTypeName)
        {

            var sqlStr = string.Empty;
            string sqlQuery = @"
            CREATE TABLE #TempFilteredOrders2 (
                orderId BIGINT,
                sysid BIGINT,
                orderAmount DECIMAL(10, 2),
                status INT
            );

            INSERT INTO #TempFilteredOrders2
            SELECT orderId, sysid, orderAmount, status 
            FROM CoreCmsOrder 
            WHERE orderTypeName = '" + orderTypeName + "'  AND createTime BETWEEN '" + start + "' AND '" + end + "' AND (signTime BETWEEN '" + start + "' AND '" + end + "' OR signTime IS NULL)" +
              " AND (sendTime BETWEEN '" + start + "' AND '" + end + "' OR sendTime IS NULL)" +
              "AND (completeTime BETWEEN '" + start + "' AND '" + end + "' OR completeTime IS NULL)" +
              " AND (refundTime BETWEEN '" + start + "' AND '" + end + "' OR refundTime IS NULL);" +
              "SELECT c.companyName, d.depName, g.groupName, u.nickName, SUM(o.orderAmount) AS val, COUNT(o.orderId) AS num," +
              " SUM(CASE WHEN o.status = 8 THEN o.orderAmount ELSE 0 END) AS signedAmount," +
              " SUM(CASE WHEN o.status = 8 THEN 1 ELSE 0 END) AS signedCount," +
              " SUM(CASE WHEN o.status = 6 THEN o.orderAmount ELSE 0 END) AS sendAmount," +
              "SUM(CASE WHEN o.status = 6 THEN 1 ELSE 0 END) AS sendCount," +
              " SUM(CASE WHEN o.status = 2 THEN o.orderAmount ELSE 0 END) AS completeAmount," +
              "  SUM(CASE WHEN o.status = 2 THEN 1 ELSE 0 END) AS completeCount," +
              "  SUM(CASE WHEN o.status = 9 THEN o.orderAmount ELSE 0 END) AS refundAmount," +
              "  SUM(CASE WHEN o.status = 9 THEN 1 ELSE 0 END) AS refundCount" +
              "  FROM #TempFilteredOrders2 o INNER JOIN SysUser u ON o.sysid = u.id INNER JOIN CoreCmsGroup g ON u.groupId = g.id  " +
              "INNER JOIN CoreCmsDepartment d ON g.dId = d.id INNER JOIN CoreCmsCompany c ON d.companyid = c.id" +
              "  GROUP BY c.companyName, d.depName, g.groupName, u.nickName ORDER BY c.companyName, d.depName, g.groupName, u.nickName;  " +
              "DROP TABLE #TempFilteredOrders2;";
            var sp = new List<SugarParameter>();

            var list = _dal.SqlQuery(sqlQuery, sp);
            return list;

        }
    }
}
